package com.ebix.sample.dao;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;
import java.util.ResourceBundle;

import javax.naming.NamingException;

import com.ebix.sample.dao.helper.DAOHelper;
import com.ebix.sample.excetion.WisException;
import com.ebix.sample.to.CorretorTO;
import com.ebix.sample.to.CotacaoTO;
import com.ebix.sample.to.FiltroCotacaoTO;
import com.ebix.sample.to.FiltroModeloTO;
import com.ebix.sample.to.RetornoBuscaCotacao;
import com.ebix.sample.to.SeguradoTO;
import com.ebix.sample.to.VeiculoCotacaoTO;

public class CotacaoDAO {

	private static ResourceBundle b = ResourceBundle//
			.getBundle("com.ebix.sample.dao.CotacaoDAO");

	public RetornoBuscaCotacao getCotacao(FiltroCotacaoTO filtro)
			throws WisException {
		Connection con = null;
		PreparedStatement st = null;
		ResultSet rs = null;
		try {
			RetornoBuscaCotacao ret = new RetornoBuscaCotacao();
			con = DAOHelper.INSTANCE.getConnection();
			List<CotacaoTO> list = new LinkedList<CotacaoTO>();
			st = con.prepareStatement(b.getString("select-cotacao"));
			st.setString(1, "%" + filtro.getInsuredName() + "%");
			st.setString(2, "%" + filtro.getVehicleModel() + "%");
			st.setString(3, "%" + filtro.getInsuredName() + "%");
			st.setString(4, "%" + filtro.getVehicleModel() + "%");
			st.setInt(5, filtro.getStart());
			st.setInt(6, filtro.getOffset());
			rs = st.executeQuery();
			while (rs.next()) {
				CotacaoTO c = new CotacaoTO(rs.getInt(1));
				c.setCorretor(new CorretorTO(rs.getInt(2), //
						rs.getString(3)));
				c.setSegurado(new SeguradoTO(rs.getInt(4), //
						rs.getString(5), //
						rs.getLong(6), //
						rs.getDate(7).getTime()));
				c.setVeiculo(new VeiculoCotacaoTO(rs.getInt(8),//
						rs.getString(9), //
						rs.getString(10)));
				list.add(c);
				ret.setNumCot(rs.getInt(11));
			}
			ret.setCotacoes(list);
			return ret;
		} catch (NamingException e) {
			throw new WisException(e);
		} catch (SQLException e) {
			throw new WisException(e);
		} finally {
			DAOHelper.INSTANCE.close(con, st, rs);
		}
	}

	public void salvarSegurado(Connection con, SeguradoTO segurado)
			throws WisException {
		PreparedStatement st = null;
		ResultSet rs = null;
		try {
			if (segurado.getId() == 0)
				st = con.prepareStatement(b.getString("insert-segurado"),//
						PreparedStatement.RETURN_GENERATED_KEYS);
			else
				st = con.prepareStatement(b.getString("update-segurado"));
			st.setString(1, segurado.getNome());
			st.setLong(2, segurado.getCpf());
			st.setDate(3, new Date(segurado.getDataNascimento()));
			if (segurado.getId() != 0)
				st.setInt(4, segurado.getId());
			st.executeUpdate();
			if (segurado.getId() == 0) {
				rs = st.getGeneratedKeys();
				if (rs.next())
					segurado.setId(rs.getInt(1));
			}
		} catch (SQLException e) {
			throw new WisException(e);
		} finally {
			DAOHelper.INSTANCE.close(st, rs);
		}
	}

	// public void salvarveiculo(Connection con, VeiculoCotacaoTO veiculo)
	// throws WisException {
	// PreparedStatement st = null;
	// ResultSet rs = null;
	// try {
	// st = con.prepareStatement(b.getString("insert-veiculo"),//
	// PreparedStatement.RETURN_GENERATED_KEYS);
	// st.setString(1, veiculo.getMarca());
	// st.setString(2, veiculo.getModelo());
	// st.executeUpdate();
	// rs = st.getGeneratedKeys();
	// if (rs.next())
	// veiculo.setId(rs.getInt(1));
	// } catch (SQLException e) {
	// throw new WisException(e);
	// } finally {
	// DAOHelper.INSTANCE.close(st, rs);
	// }
	// }

	public void salvarCotacao(CotacaoTO cotacao) throws WisException {
		Connection con = null;
		PreparedStatement st = null;
		ResultSet rs = null;
		try {
			con = DAOHelper.INSTANCE.getConnection();
			// salvarveiculo(con, cotacao.getVeiculo());
			salvarSegurado(con, cotacao.getSegurado());
			st = con.prepareStatement(b.getString("insert-cotacao"),//
					PreparedStatement.RETURN_GENERATED_KEYS);
			st.setInt(1, cotacao.getCorretor().getId());
			st.setInt(2, cotacao.getSegurado().getId());
			st.setInt(3, cotacao.getVeiculo().getId());
			st.executeUpdate();
			rs = st.getGeneratedKeys();
			if (rs.next())
				cotacao.setId(rs.getInt(1));
		} catch (NamingException e) {
			throw new WisException(e);
		} catch (SQLException e) {
			throw new WisException(e);
		} finally {
			DAOHelper.INSTANCE.close(con, st, rs);
		}
	}

	public List<String> getMarcas(String m) throws WisException {
		Connection con = null;
		PreparedStatement st = null;
		ResultSet rs = null;
		try {
			List<String> marcas = new LinkedList<String>();
			con = DAOHelper.INSTANCE.getConnection();
			st = con.prepareStatement(b.getString("select-marca"));
			st.setString(1, "%" + m + "%");
			rs = st.executeQuery();
			while (rs.next())
				marcas.add(rs.getString(1));
			return marcas;
		} catch (NamingException e) {
			throw new WisException(e);
		} catch (SQLException e) {
			throw new WisException(e);
		} finally {
			DAOHelper.INSTANCE.close(con, st, rs);
		}
	}

	public List<VeiculoCotacaoTO> getModelos(FiltroModeloTO filtro)
			throws WisException {
		Connection con = null;
		PreparedStatement st = null;
		ResultSet rs = null;
		try {
			List<VeiculoCotacaoTO> modelos = new LinkedList<VeiculoCotacaoTO>();
			con = DAOHelper.INSTANCE.getConnection();
			st = con.prepareStatement(b.getString("select-modelo"));
			st.setString(1, filtro.getMarca());
			st.setString(2, "%" + filtro.getModelo() + "%");
			rs = st.executeQuery();
			while (rs.next())
				modelos.add(new VeiculoCotacaoTO(rs.getInt(1), //
						rs.getString(2), rs.getString(3)));
			return modelos;
		} catch (NamingException e) {
			throw new WisException(e);
		} catch (SQLException e) {
			throw new WisException(e);
		} finally {
			DAOHelper.INSTANCE.close(con, st, rs);
		}
	}
}
